package com.cn.bulu.utils;

import com.cn.bulu.utils.ClothCurtain.Arith;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SplitWorkerSpace {

	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("加载数据库驱动成功");

			 String url = "jdbc:mysql://139.129.245.200:3306/bulu";//
//			 声明数据库test的url
			 String user = "root";// 数据库账号
			 String pass = "?-osRIuwb7wKsiY^@>&sW";// 数据库密码

//			 String url = "jdbc:mysql://192.168.0.88:3306/liuchaoya";//
////			 声明数据库test的url
//			 String user = "root";// 数据库账号
//			 String pass = "root";// 数据库密码
//			47.94.239.195:3306/bulu            lukebang    Lukebang@123456
//			 String url = "jdbc:mysql://47.94.239.195:3306/bulu";//
////			 声明数据库test的url
//			 String user = "lukebang";// 数据库账号
//			 String pass = "Lukebang@123456";// 数据库密码

//			String url = "jdbc:mysql://qdm165426444.my3w.com:3306/qdm165426444_db";// 声明数据库test的url
//			String user = "qdm165426444";// 数据库账号
//			String pass = "loveyouself1989";// 数据库密码
			

			// 建立数据库连接，获得连接对象conn
			Connection con = DriverManager.getConnection(url, user, pass);
			System.out.println("数据库连接成功");
			Statement stmt = con.createStatement();// 创建一个Statement对象

			// ----------------------------------------------------
			Long startTime = System.currentTimeMillis();
			SplitWorkerSpace space = new SplitWorkerSpace();
			space.calcImageScale(con);// (con);
			Long stopTime = System.currentTimeMillis();
			System.out.println("本次操作耗时===>" + (stopTime - startTime) + "毫秒");
			// ---------------------------------------------------------

			System.out.println("获得查询结果集");
			con.close();
			System.out.println("关闭数据库连接对象");
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	private void addCodeToWorkerSpaces(Statement stmt) {
		// TODO Auto-generated method stub
		try {

			// INSERT INTO picture(img,workerId) VALUES(#{img},#{workerId})
			String sql0 = "select workerId from worker_workerSpace";
			ResultSet set = stmt.executeQuery(sql0);
			int num = 0;
			while (set.next()) {
				num++;
			}
			for (int i = 0; i < num; i++) {
				String sql1 = "select * from worker_workerSpace limit " + i + ",1";
				ResultSet set1 = stmt.executeQuery(sql1);
				// 获取循环到的该条数据的workerId和workerSpace
				int workerId = -1;
				String workerSpace = "";
				while (set1.next()) {
					workerId = set1.getInt("workerId");
					workerSpace = set1.getString("workerSpace");
					System.out.println("workerId=" + workerId + ",workerSpace=" + workerSpace);
				}

				if (workerSpace == null) {
					continue;
				}

				// 该工人所在城市code
				String sql2 = "select cityCode from worker where workerId =" + workerId;
				ResultSet set2 = stmt.executeQuery(sql2);
				String cityCode = "";
				if (set2.next()) {
					cityCode = set2.getString("cityCode");
					System.out.println("cityCode=" + cityCode);
				} else {
					continue;
				}

				// 工人工作区域code查询
				System.out.println("开始执行sql3");
				String sql3 = "select code from chinaAreaTwo where parentId=" + "'" + cityCode + "'" + " and name="
						+ "\"" + workerSpace + "\"";
				ResultSet set3 = stmt.executeQuery(sql3);
				System.out.println("执行完毕sql3");
				String workerSpaceCode = "";
				while (set3.next()) {
					workerSpaceCode = set3.getString("code");
					System.out.println("workerSpaceCode=" + workerSpaceCode);
				}
				// 插入worker_workerSpace表中
				String sql4 = "update worker_workerSpace set workerSpaceCode = " + "\"" + workerSpaceCode + "\""
						+ " where workerId = " + workerId + " and workerSpace = " + "\"" + workerSpace + "\"";
				stmt.execute(sql4);
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	public void addDateToCostRule(Statement stmt) {
		try {

			// INSERT INTO picture(img,workerId) VALUES(#{img},#{workerId})
			String sql0 = "select territoryId from territory";
			ResultSet set = stmt.executeQuery(sql0);
			int num = 0;
			while (set.next()) {
				num++;
			}
			for (int i = 0; i < num; i++) {
				String sql1 = "select territoryId from territory limit " + i + ",1";
				ResultSet set1 = stmt.executeQuery(sql1);
				int territoryId = -1;
				while (set1.next()) {
					territoryId = set1.getInt("territoryId");
				}
				String type = "";

				for (int j = 0; j < 8; j++) {
					switch (j) {
					case 0:
						type = "罗马杆";
						break;
					case 1:
						type = "直轨";
						break;
					case 2:
						type = "壁钩";
						break;
					case 3:
						type = "挂绳";
						break;
					case 4:
						type = "魔术轨";
						break;
					case 5:
						type = "弯轨";
						break;
					case 6:
						type = "四爪钩";
						break;
					case 7:
						type = "电机";
						break;
					default:
						break;
					}
					String sql = "insert into cost_rules(single,doubleDeck,basisMeter,beyondMeter,meterPrice,type,motorNumber,territoryId) values("
							+ (i + 1) + "," + (i + 1) + "," + (i + 1) + "," + (i + 1) + "," + (i + 1) + "," + "\""
							+ type + "\"" + "," + (i + 1) + "," + territoryId + ")";
					stmt.execute(sql);
				}

			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void sliptSpace(Statement stmt) {
		try {
			String sql0 = "select workerId from worker";
			ResultSet set = stmt.executeQuery(sql0);
			int num = 0;
			while (set.next()) {
				num++;
			}
			System.out.println("num=" + num);
			for (int i = 0; i < num; i++) {
				String sql1 = "select * from worker limit " + i + ",1";
				ResultSet set1 = stmt.executeQuery(sql1);
				int workerId = -1;
				String workerSpace = "";
				String workerSpaceCodeStrings = "";
				String province = "";
				String city = "";
				while (set1.next()) {
					workerId = set1.getInt("workerId");
					workerSpace = set1.getString("workerSpace");
					workerSpaceCodeStrings = set1.getString("workerSpaceCodeStr");
					System.out.println("workerId=" + workerId + ",workerSpace=" + workerSpace + ",workerSpaceCodeStr="
							+ workerSpaceCodeStrings);
				}
				if (workerSpace == null) {
					continue;
				}
				if (workerSpaceCodeStrings != null) {
					continue;
				}
				if (workerSpace.indexOf("省") != -1 && workerSpace.indexOf("省") != workerSpace.length() - 1) {
					province = workerSpace.substring(0, workerSpace.indexOf("省") + 1);
					city = workerSpace.substring(workerSpace.indexOf("省") + 1, workerSpace.length());
				} else if (workerSpace.indexOf("区") != -1 && workerSpace.indexOf("区") != workerSpace.length() - 1) {
					province = workerSpace.substring(0, workerSpace.indexOf("区") + 1);
					city = workerSpace.substring(workerSpace.indexOf("区") + 1, workerSpace.length());
				} else if (workerSpace.indexOf("市") != -1 && workerSpace.indexOf("市") != workerSpace.length() - 1) {
					province = workerSpace.substring(0, workerSpace.indexOf("市") + 1);
					city = workerSpace.substring(workerSpace.indexOf("市") + 1, workerSpace.length());
				}
				if ("襄樊市".equals(city)) {
					city = "襄阳市";
				}
				System.out.println("province=" + province + ",city=" + city);

				int provinceCode = -1;
				String sql3 = "select code from chinaAreaTwo where name=" + "\"" + province + "\"" + "and level = 1";
				ResultSet set2 = stmt.executeQuery(sql3);
				while (set2.next()) {
					provinceCode = set2.getInt("code");

				}
				int cityCode = -1;
				String sql4 = "select code from chinaAreaTwo where name=" + "\"" + city + "\"" + "and level = 2";
				ResultSet set3 = stmt.executeQuery(sql4);
				while (set3.next()) {
					cityCode = set3.getInt("code");
				}
				String workerSpaceCodeStr = provinceCode + "/" + cityCode;
				System.out.println("workerSpaceCodeStr=" + workerSpaceCodeStr + ",provinceCode=" + provinceCode
						+ ",cityCode=" + cityCode);
				String sql2 = "update worker set workerSpaceCodeStr=" + "\"" + workerSpaceCodeStr + "\""
						+ ",provinceCode=" + provinceCode + ",cityCode=" + cityCode + " where workerId=" + workerId;
				stmt.executeUpdate(sql2);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void test1(Connection con) throws Exception {
		// 初始化code值
		int code = 0;
		// 新建一个province map
		Map<String, Integer> provinceMap = new HashMap<String, Integer>();
		// 新建一个城市Map
		Map<String, Integer> cityMap = new HashMap<String, Integer>();

		// 查询所有的省份
		// 创建SAXReader对象
		SAXReader reader = new SAXReader();
		// 读取文件 转换成Document
		Document document = reader.read(new File("F:\\province_data.xml"));
		// 获取根节点元素对象
		Element root = document.getRootElement();
		List<Element> elements = root.elements();
		for (Element element : elements) {
			code++;
			// 插入省份 code自增 id自增加
			String insertProvince = "insert into chinaareatwo (name,code,parentID,level) values(?,?,?,?)";
			PreparedStatement prepareStatement = con.prepareStatement(insertProvince);
			prepareStatement.setString(1, element.attribute("name").getText());
			prepareStatement.setString(2, String.valueOf(code));
			prepareStatement.setInt(3, 0);
			prepareStatement.setInt(4, 1);
			prepareStatement.executeUpdate();
			provinceMap.put(element.attribute("name").getText(), code);

			List<Element> elements2 = element.elements();
			for (Element element2 : elements2) {
				code++;
				// 插入城市
				String insertCity = "insert into chinaareatwo (name,code,parentID,level) values(?,?,?,?)";
				PreparedStatement prepareStatement2 = con.prepareStatement(insertCity);
				prepareStatement2.setString(1, element2.attribute("name").getText());
				prepareStatement2.setString(2, String.valueOf(code));
				prepareStatement2.setInt(3, provinceMap.get(element.attribute("name").getText()));
				prepareStatement2.setInt(4, 2);
				prepareStatement2.executeUpdate();
				cityMap.put(element2.attribute("name").getText(), code);
				List<Element> elements3 = element2.elements();
				for (Element element3 : elements3) {
					code++;
					// 插入城市
					String insertCountry = "insert into chinaareatwo (name,code,parentID,level) values(?,?,?,?)";
					PreparedStatement prepareStatement3 = con.prepareStatement(insertCountry);
					prepareStatement3.setString(1, element3.attribute("name").getText());
					prepareStatement3.setString(2, element3.attribute("zipcode").getText());
					prepareStatement3.setInt(3, cityMap.get(element2.attribute("name").getText()));
					prepareStatement3.setInt(4, 3);
					prepareStatement3.executeUpdate();
				}

			}
		}
	}

	public void addTrueDataToCostRule(Connection connection) {
		try {
			String queryProvinces = "select territoryId from territory";
			PreparedStatement statement = connection.prepareStatement(queryProvinces);
			ResultSet executeQuery = statement.executeQuery();
			List<Integer> list = new ArrayList<>();
//			35
//			36
//			85
//			86
//			89
//			90
//			111
//			112
//			575
//			576
			list.add(35);
			list.add(36);
			list.add(575);
			list.add(576);
			list.add(89);
			list.add(90);
			list.add(111);
			list.add(112);
			list.add(85);
			list.add(86);
			while (executeQuery.next()) {
				Integer territoryId = executeQuery.getInt("territoryId");
				double singlePrice = 0;// 单层基础价格
				double doubleDeckPrice = 0;// 双层基础价格
				double widthBasisMeter = 4;// 基础宽
				double heightBasisMeter = 3.5;// 基础高
				double widthBeyondMeter = 3;// 宽超出基础 计算单位
				double heightBeyondMeter = 0.5;// 高超出基础 计算单位
				double heightBeyondUnitPrice = 10;// 高超出基础 计算单位，每单位价格
				int meaBaseNum = 10;// 测量窗帘数量基础数量
				int meaBeyondNumOne = 10;// 第一阶段进入第二阶段计费的阈值
				int meaBeyondNumTwo = 20;// 第二阶段进入第三阶段计费的阈值
				double meaBeyUnitPriceOne = 5;// 窗帘测量数量超出基础第一阶段单位价格（如11-20）
				double meaBeyUnitPriceTwo = 3;// 窗帘测量数量超出基础第二阶段单位价格（如20以上）
				double meaStartingFare;// 测量起步价
				double installStartingFare;// 安装起步价
				double repairStartingFare;// 维修起步价
				double slingPrice = 5;// 挂绳价格
				double crampetPrice = 5;// 壁钩价格
				double fourClawPrice = 5;// 四爪钩价格
				double motorPrice = 50;// 电机价格
				double takeGoodsPrice = 20;// 工人带货到工地每单单价
				double punishWorker = 20;// 师傅取消订单罚款
				double punishClient = 50;// 客户取消订单罚款

				if (list.contains(territoryId)) {// 北上广深圳东莞价格
					meaStartingFare = 70;// 测量起步价
					installStartingFare = 80;// 安装起步价
					repairStartingFare = 80;
				} else {// 其他地区价格
					meaStartingFare = 60;// 测量起步价
					installStartingFare = 70;// 安装起步价
					repairStartingFare = 70;
				}
				String type = "";
				for (int j = 0; j < 7; j++) {
					switch (j) {
					case 0:
						type = "罗马杆";
						if (territoryId % 2 == 0) {//被2整除证明territoryId为偶数，是商家价
							singlePrice = 22;
							doubleDeckPrice = 32;
						}else if (territoryId % 2 == 1) {
							singlePrice = 20;
							doubleDeckPrice = 30;
						}
						break;
					case 1:
						type = "直轨";
						if (territoryId % 2 == 0) {//被2整除证明territoryId为偶数，是商家价
							singlePrice = 22;
							doubleDeckPrice = 32;
						}else if (territoryId % 2 == 1) {
							singlePrice = 20;
							doubleDeckPrice = 30;
						}
						break;
					case 2:
						type = "魔术轨";
						if (territoryId % 2 == 0) {//被2整除证明territoryId为偶数，是商家价
							singlePrice = 32;
							doubleDeckPrice = 42;
						}else if (territoryId % 2 == 1) {
							singlePrice = 30;
							doubleDeckPrice = 40;
						}
						break;
					case 3:
						type = "弯轨";
						if (territoryId % 2 == 0) {//被2整除证明territoryId为偶数，是商家价
							singlePrice = 27;
							doubleDeckPrice = 37;
						}else if (territoryId % 2 == 1) {
							singlePrice = 25;
							doubleDeckPrice = 35;
						}
						break;
					case 4:
						type = "电动";
						if (territoryId % 2 == 0) {//被2整除证明territoryId为偶数，是商家价
							singlePrice = 22;
							doubleDeckPrice = 32;
						}else if (territoryId % 2 == 1) {
							singlePrice = 20;
							doubleDeckPrice = 30;
						}
						break;
					case 5:
						type = "帘头";
						singlePrice = 20;//独立帘头
						doubleDeckPrice = 10;//一体帘头
						break;
					case 6:
						type = "成品帘";
						if (territoryId % 2 == 0) {//被2整除证明territoryId为偶数，是商家价
							singlePrice = 22;
							doubleDeckPrice = 22;
						}else if (territoryId % 2 == 1) {
							singlePrice = 20;
							doubleDeckPrice = 20;
						}
						break;
					default:
						break;
					}

					String sql = "insert into cost_rules(single,doubleDeck,widthBasisMeter,heightBasisMeter,"
							+ "widthBeyondMeter,heightBeyondMeter,heightBeyondUnitPrice,meaBaseNum,meaBeyondNumOne,meaBeyondNumTwo,meaBeyUnitPriceOne,"
							+ "meaBeyUnitPriceTwo,repairStartingFare,installStartingFare,meaStartingFare,slingPrice,crampetPrice,fourClawPrice,motorPrice,takeGoodsPrice,type,punishWorker,punishClient,territoryId) "
							+ "values(" + singlePrice + "," + doubleDeckPrice + "," + widthBasisMeter + ","
							+ heightBasisMeter + "," + widthBeyondMeter + "," + heightBeyondMeter + ","
							+ heightBeyondUnitPrice + "," + meaBaseNum + "," + meaBeyondNumOne + "," + meaBeyondNumTwo
							+ "," + meaBeyUnitPriceOne + "," + meaBeyUnitPriceTwo + "," + repairStartingFare + ","
							+ installStartingFare + "," + meaStartingFare + "," + slingPrice + "," + crampetPrice + ","
							+ fourClawPrice + "," + motorPrice + "," + takeGoodsPrice + ",'" + type + "',"
							+ punishWorker + "," + punishClient + "," + territoryId + ")";
					PreparedStatement prepareStatement = connection.prepareStatement(sql);
					System.out.println("hehhe " + sql);
					prepareStatement.executeUpdate();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}
	}

	private void doTerritory(Connection con) {
		// TODO Auto-generated method stub
		// 工人工作区域code查询
		try {

			Statement stmt1 = con.createStatement();
			Statement stmt2 = con.createStatement();
			Statement stmt3 = con.createStatement();

			System.out.println("开始执行sql");
			// 查询出所有省份
			String sql = "select * from chinaAreaTwo where level = 1";
			ResultSet set = stmt1.executeQuery(sql);
			System.out.println("执行完毕sql");
			String province = "";
			String provinceCode = "";
			while (set.next()) {
				province = set.getString("name");
				provinceCode = set.getString("code");
				String sql2 = "select * from chinaAreaTwo where parentId = " + provinceCode;
				ResultSet set2 = stmt2.executeQuery(sql2);
				while (set2.next()) {
					String city = set2.getString("name");
					String cityCode = set2.getString("code");
					for (int i = 0; i < 2; i++) {
						String cityCodeStr = provinceCode + "/" + cityCode;
						String sql3 = "insert into territory(province,city,cityCode,type,extract,relievedPrice) values('"
								+ province + "','" + city + "','" + cityCodeStr + "'," + i + "," + 0 + "," + 5 + ")";
						stmt3.execute(sql3);
						System.out.println("doTerritory===sql3===" + sql3);
					}
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}


	public void calcImageScale(Connection con) throws Exception {
		String insertCountry = "UPDATE picture SET pictureScale=? WHERE graphicDetails=? AND pictureScale=1";
		PreparedStatement prepareStatement3 = con.prepareStatement(insertCountry);
		String filePath = "E:\\BBB商品图\\中商切图\\710022-3";
		getFiles(filePath,prepareStatement3);
	}


	/**
	 * 通过递归得到某一路径下所有的目录及其文件
	 */
	private static ArrayList<String> filelist = new ArrayList<String>();
	static void getFiles(String filePath,PreparedStatement prepareStatement3) throws Exception{
		File root = new File(filePath);
		File[] files = root.listFiles();
		for (File file : files) {
			if (file.isDirectory()) {
                /* 递归调用*/
				getFiles(file.getAbsolutePath(),prepareStatement3);
				filelist.add(file.getAbsolutePath());
			} else {
				double scale = calcScale(file.getAbsolutePath());
				System.out.println("绝对路径===>" + file.getAbsolutePath()
						+ "\n文件名===> " + file.getName() + "        比例===>" + scale);
				prepareStatement3.setDouble(1, scale);
				prepareStatement3.setString(2, file.getName());
				prepareStatement3.executeUpdate();
			}
		}
	}
	private static double calcScale(String imagePath) throws Exception{
		BufferedImage bufferedImage = ImageIO.read(new File(imagePath));
		int width = bufferedImage.getWidth();
		int height = bufferedImage.getHeight();
		double scale = Arith.div(height, width);
		return scale;
	}
}
